{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "NOTE:\n",
    "-----\n",
    "\n",
    "Please run the below cells first before proceeding- you'll need them soon!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/tarabalakrishnan/.local/lib/python2.7/site-packages/IPython/config.py:13: ShimWarning: The `IPython.config` package has been deprecated. You should import from traitlets.config instead.\n",
      "  \"You should import from traitlets.config instead.\", ShimWarning)\n",
      "/Users/tarabalakrishnan/.local/lib/python2.7/site-packages/IPython/utils/traitlets.py:5: UserWarning: IPython.utils.traitlets has moved to a top-level traitlets package.\n",
      "  warn(\"IPython.utils.traitlets has moved to a top-level traitlets package.\")\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: None@None'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql sqlite://"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS Movies;\n",
    "CREATE TABLE Movies(title VARCHAR(50), year INT, director VARCHAR(50), length INT);\n",
    "INSERT INTO Movies VALUES('Database Wars', 1967, 'John Joe', 123);\n",
    "INSERT INTO Movies VALUES('The Databaser', 1992, 'John Bob', 190);\n",
    "INSERT INTO Movies VALUES('Database Wars', 1998, 'John Jim', 176);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    }
   ],
   "source": [
    "%sql DROP TABLE IF EXISTS A; DROP TABLE IF EXISTS B;\n",
    "%sql CREATE TABLE A (x int, y int); CREATE TABLE B (x int, y int);\n",
    "for i in range(1,6):\n",
    "    %sql INSERT INTO A VALUES (:i, :i+1)\n",
    "for i in range(1,11,3):\n",
    "    %sql INSERT INTO B VALUES (:i, :i+2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Activity 3-1:\n",
    "------------\n",
    "\n",
    "ORDER BY semantics, set operators & nested queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Exercise #1\n",
    "-----------\n",
    "\n",
    "**Can you write the movie query from lecture as a single SFW query?**\n",
    "\n",
    "Recall that we are trying to find **all movie titles that were used for more than one movie**, and our schema for the `movies` table is:\n",
    "\n",
    "> * title STRING\n",
    "> * year INT\n",
    "> * director STRING\n",
    "> * length INT\n",
    "\n",
    "Let's try to write the nested query that solves this from lecture:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT m.title \n",
    "FROM Movies m\n",
    "WHERE m.year <> ANY(SELECT year FROM Movie WHERE title = m.title);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What?  This doesn't work?  Why?\n",
    "\n",
    "**ANY doesn't exist in SQLite!**  Can we do this query without nesting?  Write your query here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "ERROR:root:Cell magic `%%sql` not found.\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT m1.title\n",
    "FROM movies m1, movies m2\n",
    "WHERE m1.title = m2.title AND m1.year <> m2.year;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Exercise #2\n",
    "--------------------\n",
    "\n",
    "Consider the two relations $A$ and $B$ below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql SELECT * FROM A;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql SELECT * FROM B;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assuming no duplicates, can you write an `INTERSECT` query, **just over the $x$ attribute**, without using `INTERSECT` OR nested queries?  Write your query here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql SELECT A.x FROM A, B WHERE A.x = B.x;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is this operation called??\n",
    "\n",
    "Next, using set operators again as well, can you return all the _full_ tuples in $A$ and $B$ that overlap in $x$ attributes?  Write your query here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>x</th>\n",
       "        <th>y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 2), (1, 3), (4, 5), (4, 6)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT x, y FROM (\n",
    "    SELECT A.x, A.y FROM A, B WHERE A.x = B.x\n",
    "    UNION\n",
    "    SELECT B.x, B.y FROM A, B WHERE A.x = B.x\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
